export interface TableColumn {
  name: string;
  comment: string;
}

export interface ForeignKey {
  column: string;
  referenceTable: string;
  referenceColumn: string;
  comment: string;
}

export interface TableStructure {
  tableName: string;
  comment: string;
  columns: TableColumn[];
  primaryKeys: string[];
  foreignKeys: ForeignKey[];
}

export function parseSQLToERStructure(sql: string): TableStructure[] {
  const tableRegex = /CREATE TABLE\s+(\w+)\s*\((.*?)\)\s*COMMENT\s*'([^']*)'/gis;
  const results: TableStructure[] = [];

  let match;
  while ((match = tableRegex.exec(sql)) !== null) {
    const [_, tableName, tableBody, tableComment] = match;
    const lines = tableBody.split(/,(?![^()]*\))/).map(line => line.trim()).filter(Boolean);

    const columns: TableColumn[] = [];
    const primaryKeys: string[] = [];
    const foreignKeys: ForeignKey[] = [];

    for (const line of lines) {
      // PRIMARY KEY
      const pkMatch = line.match(/^PRIMARY KEY\s*\(([^)]+)\)/i);
      if (pkMatch) {
        const keys = pkMatch[1].split(',').map(k => k.trim());
        primaryKeys.push(...keys);
        continue;
      }

      // FOREIGN KEY
      const fkMatch = line.match(/^FOREIGN KEY\s*\((\w+)\)\s+REFERENCES\s+(\w+)\((\w+)\)\s+COMMENT\s+'([^']+)'/i);
      if (fkMatch) {
        foreignKeys.push({
          column: fkMatch[1],
          referenceTable: fkMatch[2],
          referenceColumn: fkMatch[3],
          comment: `'${fkMatch[4]}'`
        });
        continue;
      }

      // Column
      const colMatch = line.match(/^(\w+)\s+[\w()]+(?:\s+PRIMARY KEY)?(?:\s+COMMENT\s+'([^']*)')?/i);
      if (colMatch) {
        const name = colMatch[1];
        const comment = colMatch[2] ? `'${colMatch[2]}'` : '';
        columns.push({ name, comment });
      }
    }

    results.push({
      tableName,
      comment: `'${tableComment}'`,
      columns,
      primaryKeys,
      foreignKeys
    });
  }

  return results;
}
